#include "head.h"

struct sqlite3 *sql_stockinfo_init (char *name)
{
	/*db name start*/
    char *pPath = NULL;
    struct sqlite3 *pdb = NULL;
    char *pErrMsg = NULL;
    char dbName[10] = {0};
	strcpy(dbName,name);
	printf("%s\n",dbName);
    pPath = (char *) malloc (FILEPATH_MAX + 10);
    if (NULL == pPath)
    {
        printf("malloc error\n");
    }
    memset(pPath, 0, FILEPATH_MAX + 10);
    if (NULL == getcwd(pPath, FILEPATH_MAX))
	{
		printf("getcwd error\n");
	}
    printf("%s %d\n",pPath,(int)strlen(pPath));
    memcpy(pPath + strlen(pPath), dbName, strlen(dbName));
    printf("%s %d\n",pPath,(int)strlen(pPath));
    /*db name end*/

    //open or create db
    int iResult = sqlite3_open((const char *)pPath,&pdb);
    if(SQLITE_OK != iResult || NULL == pdb)
    {
      printf("invoke sqlite3_open function error!\n");
      free(pPath);
      pPath = NULL;
    }
	//create table
    //char sqlCreateTable[] = "create table stock (account text primary key unique,passwd text not null,u_name text not null,u_limits integer not null,u_age integer not null,u_sex text not null,u_address text not null,u_phone text not null,u_salary integer not null,u_department text not null)";
    char sqlCreateTable[] = "create table stock (s_house_no integer primary key unique,"
                            "t_temperature_max integer not null,t_temperature_min integer not NULL,"
							"t_humlidity_max integer not null,t_humlidity_min integer not null,"
							"t_illumination_max integer not null,t_illumination_min integer not null,"
							"t_x_max integer not null,t_x_min integer not null,"
							"t_y_max integer not null,t_y_min integer not null,"
							"t_z_max integer not null,t_z_min integer not null,"
							"t_battery_max integer not null,t_battery_min integer not null,"
							"t_adc_max integer not null, t_adc_min integer not null)";
    printf("%s\n", sqlCreateTable);
	iResult = sqlite3_exec(pdb, sqlCreateTable, 0, 0, &pErrMsg);
    if(SQLITE_OK != iResult)
    {
      printf("%s\n", pErrMsg);
      sqlite3_free(pErrMsg);
    }
    
    //sqlite3_free(pErrMsg);
 	//free memory
    //free(pPath);
    //pPath = NULL;
	return pdb;
}
int insert_stocktable(struct sqlite3 *pdb,struct storage_info* sinfo)
{
	//insert table
    char *pErrMsg = NULL;
	char buf[512] = {0};
	//char sqlInsert[]
    char sqlInsert[512] = {0};    
	
	sprintf(sqlInsert, "insert into stock(s_house_no,"
		"t_temperature_max,t_temperature_min,"
		"t_humlidity_max,t_humlidity_min,"
		"t_illumination_max,t_illumination_min,"
		"t_x_max,t_x_min,t_y_max,t_y_min,t_z_max,t_z_min,"
		"t_battery_max,t_battery_min,t_adc_max,t_adc_min)"
		"values('%d','%d','%d','%d','%d','%d','%d','%d',"
		"'%d','%d','%d','%d','%d','%d','%d','%d','%d')", sinfo->s_house_no, sinfo->s_threshold.t_temperature_max,
		sinfo->s_threshold.t_temperature_min, sinfo->s_threshold.t_humlidity_max, sinfo->s_threshold.t_humlidity_min,
		sinfo->s_threshold.t_illumination_max, sinfo->s_threshold.t_illumination_min,
		sinfo->s_threshold.t_x_max, sinfo->s_threshold.t_x_min,
		sinfo->s_threshold.t_y_max, sinfo->s_threshold.t_y_min,
		sinfo->s_threshold.t_z_max, sinfo->s_threshold.t_z_min,
		sinfo->s_threshold.t_battery_max, sinfo->s_threshold.t_battery_min,
		sinfo->s_threshold.t_adc_max, sinfo->s_threshold.t_adc_min);
	
	printf("%s\n",sqlInsert);
    int iResult = sqlite3_exec(pdb, sqlInsert, 0, 0, &pErrMsg);
    if(SQLITE_OK != iResult)
    {
      printf("%s\n", pErrMsg);
      printf("lixiang\n");
	  //sqlite3_free(pErrMsg);
	  return -1;
    }
	printf("=====\n");
	
	return 0;
    //sqlite3_free(pErrMsg);
}
int close_db (struct sqlite3 *pdb)
{
	//close db
    int iResult = sqlite3_close(pdb);
    
    if(SQLITE_OK != iResult)
    {
      return -1;
    }
}

struct storage_info* select_stockdb(struct sqlite3 *pdb, struct storage_info * sinfo)
{
    //select table
    char sqlSelect[] = "select * from stock";
    char** ppTable = NULL;
	char *pErrMsg = NULL;
    int iRow = 0;
    int iColumn = 0;
	char i_a[32] = {0};
    int iResult = sqlite3_get_table(pdb, sqlSelect, &ppTable, &iRow, &iColumn,&pErrMsg);
	
    if (SQLITE_OK != iResult)
    {
        printf("%s\n", pErrMsg);
    }
    sqlite3_free(pErrMsg);
    printf("select sql row %d column %d\n", iRow, iColumn);
	//printf("AAAAAAAAAAAAAAAAAAAAAAAAAAA\n");
	if (NULL != ppTable)
    {
		int i = (sinfo->s_house_no);
		int j = 0;
		for (; j < iColumn; j++)
		{
			if (NULL != ppTable[i * iColumn + j])
			{
				printf("%s    ",ppTable[i * iColumn + j]);
				switch (j)
				{
					case 0:
						sinfo->s_house_no = atoi(ppTable[i * iColumn +j]);
						break;
					case 1:
						sinfo->s_threshold.t_temperature_max = atoi(ppTable[i * iColumn +j]);
						break;
					case 2:
						sinfo->s_threshold.t_temperature_min = atoi(ppTable[i * iColumn +j]);
						break;
					case 3:
						sinfo->s_threshold.t_humlidity_max = atoi(ppTable[i * iColumn +j]);
						break;
					case 4:
						sinfo->s_threshold.t_humlidity_min = atoi(ppTable[i * iColumn +j]);
						break;
					case 5:
						sinfo->s_threshold.t_illumination_max = atoi(ppTable[i * iColumn +j]);
						break;
					case 6:
						sinfo->s_threshold.t_illumination_min = atoi(ppTable[i * iColumn +j]);
						break;
					case 7:
						sinfo->s_threshold.t_x_max = atoi(ppTable[i * iColumn +j]);
						break;
					case 8:
						sinfo->s_threshold.t_x_min = atoi(ppTable[i * iColumn +j]);
						break;
					case 9:
						sinfo->s_threshold.t_y_max = atoi(ppTable[i * iColumn +j]);
						break;
					case 10:
						sinfo->s_threshold.t_y_min = atoi(ppTable[i * iColumn +j]);
						break;
					case 11:
						sinfo->s_threshold.t_z_max = atoi(ppTable[i * iColumn +j]);
						break;
					case 12:
						sinfo->s_threshold.t_z_min = atoi(ppTable[i * iColumn +j]);
						break;
					case 13:
						sinfo->s_threshold.t_battery_max = atoi(ppTable[i * iColumn +j]);
						break;
					case 14:
						sinfo->s_threshold.t_battery_min = atoi(ppTable[i * iColumn +j]);
						break;
					case 15:
						sinfo->s_threshold.t_adc_max = atoi(ppTable[i * iColumn +j]);
						break;
					case 16:
						sinfo->s_threshold.t_adc_min = atoi(ppTable[i * iColumn +j]);
						break;
					default:
						break;
				}
			}
		}
		printf("\n");
    }
	sqlite3_free_table(ppTable);
	return sinfo;
}

int select_db(struct sqlite3 *pdb)
{
    //select table
    char sqlSelect[] = "select * from stock";
    char** ppTable = NULL;
	char *pErrMsg = NULL;
    int iRow = 0;
    int iColumn = 0;
	char i_a[32] = {0};
    int iResult = sqlite3_get_table(pdb, sqlSelect, &ppTable, &iRow, &iColumn,&pErrMsg);
	
    if (SQLITE_OK != iResult)
    {
        printf("%s\n", pErrMsg);
    }
    sqlite3_free(pErrMsg);
    printf("select sql row %d column %d\n", iRow, iColumn);
	if (NULL != ppTable)
    {
        int i = 1;
        for (; i <= iRow; i++)
        {
            int j = 0;
            for (; j < iColumn; j++)
            {
                if (NULL != ppTable[i * iColumn + j])
                {
                    printf("%s    ",ppTable[i * iColumn + j]);
                }
            }
            printf("\n");
        }
    }
	sqlite3_free_table(ppTable);
}

int updata_stocktable(struct sqlite3 *pdb,struct storage_info* sinfo)
{
    char *pErrMsg = NULL;
    char buf[128] = {0};
	char sqlInsert[1024] = {0};
	sprintf(sqlInsert, "update stock set t_temperature_max = %d,"
			"t_temperature_min = %d, t_humlidity_max = %d, t_humlidity_min = %d,"
			"t_illumination_max = %d, t_illumination_min = %d,"
			"t_x_max = %d, t_x_min = %d, t_y_max = %d, t_y_min = %d,"
			"t_z_max = %d, t_z_min = %d, t_battery_max = %d, t_battery_min = %d,"
			"t_adc_max = %d, t_adc_min = %d where s_house_no = %d",
			sinfo->s_threshold.t_temperature_max,
			sinfo->s_threshold.t_temperature_min,
			sinfo->s_threshold.t_humlidity_max,
			sinfo->s_threshold.t_humlidity_min,
			sinfo->s_threshold.t_illumination_max,
			sinfo->s_threshold.t_illumination_min,
			sinfo->s_threshold.t_x_max,
			sinfo->s_threshold.t_x_min,
			sinfo->s_threshold.t_y_max,
			sinfo->s_threshold.t_y_min,
			sinfo->s_threshold.t_z_max,
			sinfo->s_threshold.t_z_min,
			sinfo->s_threshold.t_battery_max,
			sinfo->s_threshold.t_battery_min,
			sinfo->s_threshold.t_adc_max,
			sinfo->s_threshold.t_adc_min,
			sinfo->s_house_no);

	//printf("%s\n",sqlInsert);
    int iResult = sqlite3_exec(pdb, sqlInsert, 0, 0, &pErrMsg);
    if(SQLITE_OK != iResult)
    {
      printf("%s\n", pErrMsg);
      sqlite3_free(pErrMsg);
	  return -1;
	}
    //sqlite3_free(pErrMsg);
	return 0;
}

int delete_stocktable(struct sqlite3 *pdb, struct storage_info* sinfo)
{
    char *pErrMsg = NULL;
    
	char sqlInsert[1024] = "delete from stock where id = ";
	sprintf(sqlInsert, "delete from stock where s_house_no = %d", sinfo->s_house_no);

	printf("%s\n",sqlInsert);
    int iResult = sqlite3_exec(pdb, sqlInsert, 0, 0, &pErrMsg);
    if(SQLITE_OK != iResult)
    {
      printf("%s\n", pErrMsg);
      sqlite3_free(pErrMsg);
	  return -1;
    }
    //sqlite3_free(pErrMsg);
	return 0;
}
